/************************************************************************************/
/*  Program name CAPFNDIMP_MASK_ALL_EXP_ONE.sql                                     */
/*  Creater:  NIP Karen (PCCW - UniHub)                                             */
/*************   History   *************************                                */
/* 9 FEB, 2006   NIP Karen         First created                                    */
/************************************************************************************/
SET SERVEROUTPUT ON SIZE 1000000;

ALTER TRIGGER AP_BANK_ACCOUNTS_CT1 DISABLE;
ALTER TRIGGER AP_BANK_ACCOUNTS_CT2 DISABLE;
ALTER TRIGGER AP_CHECKS_CBEI DISABLE;
ALTER TRIGGER AX_AP_CHECKS_ARU1 DISABLE;
ALTER TRIGGER AX_AP_CHECKS_ARU2 DISABLE;
ALTER TRIGGER AX_AP_CHECKS_BRI1 DISABLE;
ALTER TRIGGER AP_SSE_NOTIFY_EMPLOYEE DISABLE;
ALTER TRIGGER AX_AP_INVOICE_PAY_ARDI1 DISABLE;
ALTER TRIGGER AX_AP_INVOICE_PAY_ARU1 DISABLE;
ALTER TRIGGER AX_AP_INVOICE_PAY_ARU2 DISABLE;

DECLARE
	lv_Prog_ID		VARCHAR2(100)	:=	'CAPFNDIMP_MASK_ALL_EXP_ONE';

	lv_Scc_Count	NUMBER	:=	0;
	lv_Err_Count	NUMBER	:=	0;
	lv_Err_Msg		VARCHAR2(500)	:=	'No Error';

	CURSOR Cur_F(p_NGO VARCHAR2) IS
	SELECT
		B.ORGANIZATION_ID
	FROM
		HR_ALL_ORGANIZATION_UNITS		B,
		HR_ORGANIZATION_INFORMATION		I
	WHERE	SUBSTR(B.Name,4,4)	!=	UPPER(p_NGO)
	AND		B.Organization_ID	=	I.Organization_ID
	AND		I.Org_Information_Context	=	'Operating Unit Information';

	lv_NGO_Code		VARCHAR2(4);
	lv_ORG_ID		NUMBER(15);

BEGIN
	lv_NGO_Code	:=	UPPER('&NGO_Code');

	FOR C1 IN Cur_F(lv_NGO_Code) LOOP
		BEGIN
			UPDATE	AP_BANK_ACCOUNTS_ALL
			SET		BANK_ACCOUNT_NUM	=	TO_CHAR(BANK_ACCOUNT_ID)
			WHERE	ORG_ID	=	C1.ORGANIZATION_ID;

			UPDATE	AP_CHECKS_ALL
			SET		BANK_ACCOUNT_NUM	=	TO_CHAR(CHECK_ID)
			WHERE	ORG_ID	=	C1.ORGANIZATION_ID;

			UPDATE	AP_SELECTED_INVOICE_CHECKS_ALL
			SET		BANK_ACCOUNT_NUM	=	TO_CHAR(SELECTED_CHECK_ID)
			WHERE	ORG_ID	=	C1.ORGANIZATION_ID
			AND		BANK_ACCOUNT_NUM is not null;

			UPDATE	AP_SELECTED_INVOICES_ALL
			SET		BANK_ACCOUNT_NUM	=	TO_CHAR(PAY_SELECTED_CHECK_ID)
			WHERE	ORG_ID	=	C1.ORGANIZATION_ID
			AND		BANK_ACCOUNT_NUM is not null;

			UPDATE	AP_INVOICE_PAYMENTS_ALL
			SET		BANK_ACCOUNT_NUM	=	TO_CHAR(INVOICE_PAYMENT_ID)
			WHERE	ORG_ID	=	C1.ORGANIZATION_ID
			AND		BANK_ACCOUNT_NUM is not null;

		EXCEPTION
			WHEN OTHERS THEN
				DBMS_OUTPUT.Put_Line(SQLERRM);
				lv_Err_Count	:=	lv_Err_Count + 1;
		END;
		lv_Scc_Count	:=	lv_Scc_Count	+	1;
	END LOOP;

	IF	lv_Err_Count	>	0	THEN
		ROLLBACK;
	END IF;

	INSERT INTO CAPCNV_LOG(
		PROG_ID,
		PROCESS_DATE,
		RECORD_PROCESSED,
		RECORD_REJECTED,
		RECORD_EXTRACTED,
		ERR_MSG
	) VALUES (
		lv_Prog_ID,
		SYSDATE,
		lv_Scc_Count,
		lv_Err_Count,
		lv_Scc_Count-lv_Err_Count,
		lv_Err_Msg
	);

	COMMIT;
END;
/

ALTER TRIGGER AP_BANK_ACCOUNTS_CT1 ENABLE;
ALTER TRIGGER AP_BANK_ACCOUNTS_CT2 ENABLE;
ALTER TRIGGER AP_CHECKS_CBEI ENABLE;
ALTER TRIGGER AX_AP_CHECKS_ARU1 ENABLE;
ALTER TRIGGER AX_AP_CHECKS_ARU2 ENABLE;
ALTER TRIGGER AX_AP_CHECKS_BRI1 ENABLE;
ALTER TRIGGER AP_SSE_NOTIFY_EMPLOYEE ENABLE;
ALTER TRIGGER AX_AP_INVOICE_PAY_ARDI1 ENABLE;
ALTER TRIGGER AX_AP_INVOICE_PAY_ARU1 ENABLE;
ALTER TRIGGER AX_AP_INVOICE_PAY_ARU2 ENABLE;
